<?php
/**
 * Created by PhpStorm.
 * User: seven
 * Date: 2018/3/4
 * Time: 下午10:29
 */

class PDOManager3{

    static function getDB(){
        $db = new PDO("mysql:host=rm-bp16ad3ce8188drq6o.mysql.rds.aliyuncs.com;dbname=lishuai", "root", "cowthan_777");
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db -> query('SET NAMES UTF8');
        return $db;
    }

    //insert into imooc_admin(username, password, email) values ('king', 'b2086154f101464aab3328ba7e060deb', '279800561@qq.com')";

    /**
     * @param PDO $db
     * @param $table
     * @param array $contentValues
     * @return 返回刚插入的记录的的主键
     */
    static function insert(PDO $db, $table, array $contentValues){

        $sql = "insert into " . $table . "({fields}) values ({values});";
        $fields = "";
        $values = "";
        foreach ($contentValues as $key => $vv){
            $fields .= $key . ", ";
            $values .= ":" . $key . ", ";
        }
        $fields = substr($fields, 0, strlen($fields) - 2);
        $values = substr($values, 0, strlen($values) - 2);

        $sql = str_replace("{fields}", $fields, $sql);
        $sql = str_replace("{values}", $values, $sql);

        //insert into imooc_admin(username, password, email) values (:username, :password, :email)
//        echo $sql . "<br/>";

        $statement = $db->prepare($sql);
        $statement->execute($contentValues);

        return $db->lastInsertId();
    }

    static function update(PDO $db, $table, array $contentValues, $whereCondition, array $whereParams){

        $sql = "UPDATE " . $table . " SET {values} where 1=1";
        if($whereCondition){
            $sql .= " and " . $whereCondition . ";";
        }else{
            $sql .= ";";
        }


        $values = "";
        foreach ($contentValues as $key => $vv){
            $values .= "$key=:" . $key . ", ";
        }
        $values = substr($values, 0, strlen($values) - 2);

        $sql = str_replace("{values}", $values, $sql);

        //insert into imooc_admin(username, password, email) values (:username, :password, :email)
//        echo $sql . "<br/>";

        $statement = $db->prepare($sql);
        $statement->execute(array_merge($contentValues, $whereParams));

        return $statement->rowCount();
    }


    static function delete(PDO $db, $table, $whereCondition, array $whereParams){

        $sql = "delete from " . $table . " where 1=1";
        if($whereCondition){
            $sql .= " and " . $whereCondition . ";";
        }else{
            $sql .= ";";
        }

        //insert into imooc_admin(username, password, email) values (:username, :password, :email)
//        echo $sql . "<br/>";

        $statement = $db->prepare($sql);
        $statement->execute($whereParams);

        return $statement->rowCount();
    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
//     * @param $fields 指定感兴趣的列，null或空则表示对所有列感兴趣
     * @return array  返回数组
     */
    static function findAll($db, $sql, $assoc_params){
        $statement = $db->prepare($sql);
        $statement->execute($assoc_params);

        $res = array();
        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
            //$res[] = PDOManager::extractFields($row, $fields);
            $res[] = $row;
        }
        return $res;
    }

    static function findOne($db, $sql, $assoc_params){
        $statement = $db->prepare($sql);
        $statement->execute($assoc_params);

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return $row;
    }

    static function findOneField($db, $sql, $assoc_params){
        $statement = $db->prepare($sql);
        $statement->execute($assoc_params);

        $row = $statement->fetch(PDO::FETCH_NUM);
        if($row){
            return $row[0];
        }else{
            return null;
        }
    }

//    static function find($db, $table, array $columns, $where, $assoc_params){
//        $sql = 'select ' . join(",", $columns) . " from $table " . $where;
//        $statement = $db->prepare($sql);
//        $statement->execute($assoc_params);
//
//        $res = array();
//        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
//            $res[] = PDOManager::extractFields($row, $fields);
//        }
//        return $res;
//    }


    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $fileds 指定感兴趣的列，null或空则表示对所有列感兴趣
     * @return array
     */
//    static function findOne($db, $sql, $assoc_params, $fileds = null){
//        $statement = $db->prepare($sql);
//
//        $statement->execute($assoc_params);
//
//
//        $res = array();
//        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
//            $res[] = PDOManager::extractFields($row, $fileds);
//        }
//        return $res;
//
//        $row = $statement->fetch(PDO::FETCH_ASSOC);
//        if($row){
//            return PDOManager::extractFields($row, $fileds);
//        }else{
//            return null;
//        }
//    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $columnName  数据库的一个字段名，以此为值
     * @param null $columnAsKey  数据库的一个字段名，如果字段名存在且有值，则对应的列值就是返回的数组的key，否则就是下标数组
     * @return array
     */
//    static function findColumnValues($db, $sql, $assoc_params, $columnName, $columnAsKey = null){
//        $statement = $db->prepare($sql);
//        $statement->execute($assoc_params);
//
//        $res = array();
//        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
//            $value = null;
//            if(array_key_exists($columnName, $row)){
//                $value = $row[$columnName];
//            }
//
//            if($columnAsKey && array_key_exists($columnAsKey, $row) && $row[$columnAsKey]){
//                $res[$row[$columnAsKey]] = $value;
//            }else{
//                $res[] = $value;
//            }
//
//        }
//        return $res;
//    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $columnName 数据库的一个字段名，以此为值
     * @return string
     */
//    static function findColumnValue($db, $sql, $assoc_params, $columnName){
//        $statement = $db->prepare($sql);
//        $statement->execute($assoc_params);
//
//        $row = $statement->fetch(PDO::FETCH_ASSOC);
//        if($row){
//            if(array_key_exists($columnName, $row)){
//                return $row[$columnName];
//            }else{
//                return null;
//            }
//        }else{
//            return null;
//        }
//    }



    private static function extractFields($rawArray, $fields){
        if($fields == null || count($fields) == 0) return $rawArray;

        $res = array();
        foreach ($fields as $key => $value){
            if(array_key_exists($key, $rawArray)){
                $res[$key] = $rawArray[$key];
            }else{
                $res[$key] = null;
            }
        }
        return $res;
    }
}